﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using fsw.DBUtility;
using fsw.Common;
using System.Reflection;

namespace fsw.DAL
{
    public partial class Admin
    {
        private string databaseprefix; //数据库表名前缀
        public Admin(string _databaseprefix)
        {
            databaseprefix = _databaseprefix;
        }
        #region 基本操作方法================
        /// <summary>
        /// 根据id判读是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "Admin");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 根据用户名判断是否存在
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public bool Exists(string UserName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "Admin");
            strSql.Append(" where UserName=@UserName");
            SqlParameter[] parameters = {
					new SqlParameter("@UserName", SqlDbType.NVarChar,50)};
            parameters[0].Value = UserName;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 根据用户名和密码查询是否存在
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="PassWord"></param>
        /// <returns></returns>
        public bool Exists(string UserName, string PassWord)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "Admin");
            strSql.Append(" where UserName=@UserName and PassWord=@PassWord");
            SqlParameter[] parameters = {
					new SqlParameter("@UserName", SqlDbType.NVarChar,50),
                    new SqlParameter("@PassWord",SqlDbType.NVarChar,50)};
            parameters[0].Value = UserName;
            parameters[1].Value = PassWord;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(Model.Admin model)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();//打开数据连接
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        #region 主表信息==========================
                        StringBuilder strSql = new StringBuilder();
                        StringBuilder str1 = new StringBuilder();//数据字段
                        StringBuilder str2 = new StringBuilder();//数据参数
                        //利用反射获得属性的所有公共属性
                        PropertyInfo[] pros = model.GetType().GetProperties();
                        List<SqlParameter> paras = new List<SqlParameter>();
                        strSql.Append("insert into " + databaseprefix + "Admin(");
                        foreach (PropertyInfo pi in pros)
                        {
                            //如果不是主键则追加sql字符串
                            if (!pi.Name.Equals("Id"))
                            {
                                //判断属性值是否为空
                                if (pi.GetValue(model, null) != null && !typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType))
                                {
                                    str1.Append(pi.Name + ",");//拼接字段
                                    str2.Append("@" + pi.Name + ",");//声明参数
                                    paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                                }
                            }
                        }
                        strSql.Append(str1.ToString().Trim(','));
                        strSql.Append(") values (");
                        strSql.Append(str2.ToString().Trim(','));
                        strSql.Append(") ");
                        strSql.Append(";select @@IDENTITY;");
                        object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), paras.ToArray());//带事务
                        model.Id = Convert.ToInt32(obj);
                        #endregion
                        #region ========添加副表==========
                        if (model.Admin_Project != null)
                            new DAL.Admin_Project(databaseprefix).Add(conn, trans, model.Admin_Project, model.Id);
                        if (model.Admin_Mould != null)
                            new DAL.Admin_Mould(databaseprefix).Add(conn, trans, model.Admin_Mould, model.Id);
                        #endregion
                        trans.Commit(); //提交事务
                    }
                    catch
                    {
                        trans.Rollback(); //回滚事务
                        return 0;
                    }
                }
            }
            return model.Id;
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(Model.Admin model)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                 conn.Open();
                 using (SqlTransaction trans = conn.BeginTransaction())
                 {
                     try
                     {
                         #region 主表信息==========================
                         StringBuilder strSql = new StringBuilder();
                         StringBuilder str1 = new StringBuilder();
                         //利用反射获得属性的所有公共属性
                         PropertyInfo[] pros = model.GetType().GetProperties();
                         List<SqlParameter> paras = new List<SqlParameter>();
                         strSql.Append("update " + databaseprefix + "Admin set ");
                         foreach (PropertyInfo pi in pros)
                         {
                             //如果不是主键则追加sql字符串
                             if (!pi.Name.Equals("Id"))
                             {
                                 //判断属性值是否为空
                                 if (pi.GetValue(model, null) != null && !typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType))
                                 {
                                     str1.Append(pi.Name + "=@" + pi.Name + ",");//声明参数
                                     paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                                 }
                             }
                         }
                         strSql.Append(str1.ToString().Trim(','));
                         strSql.Append(" where id=@id ");
                         paras.Add(new SqlParameter("@id", model.Id));
                         DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), paras.ToArray());
                         #endregion
                         #region 副表信息
                         if (model.Admin_Project != null)
                             new DAL.Admin_Project(databaseprefix).Update(conn, trans, model.Admin_Project, model.Id);
                         if (model.Admin_Mould != null)
                             new DAL.Admin_Mould(databaseprefix).Update(conn, trans, model.Admin_Mould, model.Id);
                         #endregion
                         trans.Commit(); //提交事务
                     }
                     catch
                     {
                         trans.Rollback(); //回滚事务
                         return false;
                     }
                 }
            }
            return true;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="strValue"></param>
        /// <returns></returns>
        public bool UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "Admin set " + strValue);
            strSql.Append(" where id=" + id);
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from  " + databaseprefix + "Admin ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }
        /// <summary>
        /// 得到一个实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Model.Admin GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.Admin model = new Model.Admin();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                str1.Append(p.Name + ",");//拼接字段
            }
            strSql.Append("select top 1 *");
            strSql.Append(" from " + databaseprefix + "Admin");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;
            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 根据用户名和密码得到一个实体
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="PassWord"></param>
        /// <returns></returns>
        public Model.Admin GetModel(string UserName, string PassWord)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id from " + databaseprefix + "Admin");
            strSql.Append(" where UserName=@UserName and PassWord=@PassWord and isStatus=1");
            SqlParameter[] parameters = {
					new SqlParameter("@UserName", SqlDbType.NVarChar,100),
                    new SqlParameter("@password", SqlDbType.NVarChar,100)};
            parameters[0].Value = UserName;
            parameters[1].Value = PassWord;
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj != null)
            {
                return GetModel(Convert.ToInt32(obj));
            }
            return null;
        }
        /// <summary>
        /// 前几个帐号
        /// </summary>
        /// <param name="Top"></param>
        /// <param name="strWhere"></param>
        /// <param name="filedOrder"></param>
        /// <returns></returns>
        public List<Model.Admin> GetList(int Top,string strWhere,string filedOrder)
        {
            List<Model.Admin> modelList = new List<Model.Admin>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top " + Top.ToString() + " * from " + databaseprefix + "Admin a");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            if (filedOrder.Trim() != "")
            {
                strSql.Append(" order by " + filedOrder);
            }
            DataTable dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            if (dt.Rows.Count > 0)
            {
                for (int n = 0; n < dt.Rows.Count; n++)
                {
                    modelList.Add(DataRowToModel(dt.Rows[n]));
                }
            }
            return modelList;
        }
        /// <summary>
        /// 分页数据
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="filedOrder"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM " + databaseprefix + "Admin");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
        #endregion
        #region 扩展功能=======
        /// <summary>
        /// 将对象转换实体
        /// </summary>
        public Model.Admin DataRowToModel(DataRow row)
        {
            Model.Admin model = new Model.Admin();
            if (row != null)
            {
                #region 主表信息======================
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        //用索引值设置属性值
                        proInfo.SetValue(model, row[i], null);
                    }
                }
                #endregion
                model.Admin_Project = new DAL.Admin_Project(databaseprefix).GetList(model.Id);
                model.Admin_Mould = new DAL.Admin_Mould(databaseprefix).GetList(model.Id);
            }
            return model;
        }
        #endregion
    }
}
